package com.hkts.reportform.sodata.daoImp;

import com.hkts.reportform.sodata.dao.SoDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("sodaoImp")
public class SodaoImp implements SoDao {
    @Autowired
    private NamedParameterJdbcTemplate temple;


    /*select it.cInvName, ss.cSOCode,ss.iQuantity, CONVERT(VARCHAR(10),ss.dPreDate,21)
    FROM Inventory it  INNER JOIN SO_SODetails ss on ss.cInvCode=it.cInvCode
    WHERE ss.dPreDate >=:beginDate AND ss.dPreDate <=:endDate
    GROUP BY r.dDate order by r.dDate*/
    @Override
    public List<Map<String, Object>> getSalesOrderList(String beginDate, String endDate,String firstRow,String lastRow) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select t.*,sm.cSOCode, CONVERT(VARCHAR(100),sm.dverifysystime,23) as dverifysystime  from ( ");
        buffer.append("select a.*, ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS num from( ");
        buffer.append("select SUM(isnull(ss.iQuantity, 0.000000)) AS iQuantity, ");
        buffer.append("SUM(isnull( ss.iFHQuantity, 0.000000 )) AS iFHQuantity, ");
        buffer.append("ss.ID AS ID FROM SO_SOMain sm ");
        buffer.append("INNER JOIN SO_SODetails ss on sm.ID=ss.ID ");
        buffer.append("WHERE sm.dverifydate>=:beginDate AND sm.dverifydate<=:endDate ");
        buffer.append("and isnull( sm.cVerifier , '' ) <> '' GROUP BY ss.ID ");
        buffer.append(" ) as a  ");
        buffer.append(") as t INNER JOIN SO_SOMain sm on t.ID=sm.ID where t.num>=:firstRow and t.num<=:lastRow ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    public List<Map<String, Object>> getSalesOrderStatistics(String beginDate, String endDate){
        StringBuffer buffer = new StringBuffer();
        buffer.append("select it.cInvCode as cInvCode,it.cInvName as cInvName, isnull( ss.iFHQuantity, 0.000000) AS iFHQuantity, CONVERT(VARCHAR(100),ss.dPreDate,23)  as ddate ");
        buffer.append("FROM Inventory it  INNER JOIN SO_SODetails ss on ss.cInvCode=it.cInvCode ");
        buffer.append("WHERE ss.dPreDate >=:beginDate AND ss.dPreDate <=:endDate ");
        /*buffer.append("GROUP BY ss.dPreDate order ss.dPreDate ");*/
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getSsOdSttts(String beginDate, String endDate) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("select it.cInvCode as cInvCode,it.cInvName as cInvName, isnull( ss.iFHQuantity, 0.000000) AS iFHQuantity, RIGHT(CONVERT(varchar(12), ss.dPreDate, 111) ,5)  as ddate ");
        buffer.append("FROM Inventory it  INNER JOIN SO_SODetails ss on ss.cInvCode=it.cInvCode ");
        buffer.append("WHERE ss.dPreDate >=:beginDate AND ss.dPreDate <=:endDate ");
        /*buffer.append("GROUP BY ss.dPreDate order ss.dPreDate ");*/
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getCInvNameBySoid(String soid) {
        String sql="select i.cInvName from SO_SODetails s inner join Inventory i on s.cInvCode=i.cInvCode  where s.ID=:soid ";
        Map<String, Object> map = new HashMap<>();
        map.put("soid", soid);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getCountOrderExecution(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select count(*) as count from SO_SOMain m INNER JOIN SO_SODetails d ON m.ID= d.ID ");
        buffer.append("where m.dverifydate>=:beginDate and m.dverifydate<=:endDate ");
        buffer.append("and isnull(  m.cVerifier , '' ) <> ''  GROUP BY  d.ID ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    //销售过磅单
    @Override
    public List<Map<String, Object>> getSalesWeighingList(String beginDate, String endDate,String firstRow,String lastRow) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("SELECT a.*,f.cSVCode,f.cVehCode FROM( ");
        buffer.append("SELECT t.*,ROW_NUMBER() OVER(ORDER BY [ID] ASC) AS num FROM( ");
        buffer.append("SELECT CAST(round(isnull(f.fFirst, 0.000000),2) AS NUMERIC(5,2)) AS fFirst, ");
        buffer.append("CAST(round(isnull(f.fSecond, 0.000000),2) AS NUMERIC(5,2)) AS fSecond, ");
        buffer.append("CONVERT(VARCHAR(100),f.dFirstDate,20) AS dFirstDate, ");
        buffer.append("CAST(round(isnull(f.fSuttle, 0.000000),2) AS NUMERIC(5,2)) AS fSuttle, ");
        buffer.append("CONVERT(varchar(50), f.ID) AS ID FROM FL_SendVouch f ");
        buffer.append("where CONVERT(VARCHAR(100),dFirstDate,23)>=:beginDate AND CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate ");
        buffer.append("AND isnull( cFirster , '' ) <> '' ");
        buffer.append(") as t ) as a INNER JOIN FL_SendVouch f ON a.ID=f.ID where a.num>=:firstRow AND a.num<=:lastRow");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        map.put("firstRow", firstRow);
        map.put("lastRow", lastRow);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    //获取销售过磅单查询总数
    @Override
    public List<Map<String, Object>> getCountSalesWeighingExecution(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("select count(*) as count FROM FL_SendVouch WHERE isnull( cFirster , '' ) <> '' AND ");
        buffer.append("CONVERT(VARCHAR(100),dFirstDate,23)>=:beginDate AND CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }

    @Override
    public List<Map<String, Object>> getCInvNameByWeighingId(String id) {
        String sql="select i.cInvName from FL_SendVouch f inner join Inventory i on f.cInvCode=i.cInvCode  where f.ID=:id ";
        Map<String, Object> map = new HashMap<>();
        map.put("id", id);
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        return maps;
    }
    @Override
    public List<Map<String, Object>> getCarNumAndTunnageByData(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT sum(CAST(round(isnull(fSuttle, 0.000000),2) AS NUMERIC(5,2))) AS fSuttle FROM FL_SendVouch where ");
        buffer.append("CONVERT(VARCHAR(100),dFirstDate,23)>=:beginDate AND CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }
    @Override
    public List<Map<String, Object>> getSalesWeighingByData(String beginDate, String endDate) {
        StringBuffer buffer=new StringBuffer();
        buffer.append("SELECT count(*) count ,CONVERT(VARCHAR(100),dFirstDate,23) data ");
        buffer.append("FROM FL_SendVouch f where ");
        buffer.append("CONVERT(VARCHAR(100),dFirstDate,23)>=:beginDate AND CONVERT(VARCHAR(100),dFirstDate,23)<=:endDate ");
        buffer.append("GROUP BY CONVERT(VARCHAR(100),dFirstDate,23) ");
        Map<String, Object> map = new HashMap<>();
        map.put("beginDate", beginDate);
        map.put("endDate", endDate);
        List<Map<String, Object>> maps = temple.queryForList(buffer.toString(), map);
        return maps;
    }


}
